# -*- coding: utf-8 -*-
import codecs
import fnmatch
import hashlib
import os
from random import random
import string
from settings import STATICFILES_DIRS, STATIC_URL, db

csvHeader = (
    "main_type", "attribute_set", "sku", "name", "price", "special_price", "special_from_date", "special_to_date",)

currency_rate = 8.3000

def getCSVHeader():
    csvText = ""
    for elem in csvHeader:
        csvText += "\"" + elem + "\","
    return csvText[:len(csvText) - 1]


def validLog(username, password):
    validPassword = db.sqlQuery('select password from firstadmin_user where username=\'' + username + '\'')
    if not validPassword:
        return False
    validPassword = validPassword[0][0].split(':')
    salt = validPassword[1]
    if hashlib.md5(salt + password).hexdigest() == validPassword[0]:
        return True
    else:
        return False


def resultTuple_to_array(result):
    array = []
    for i in result:
        for j in i:
            array.append(j)
    return array


def verifyTuple(param):
    t = ()
    for i in param:
        if i:
            t = t + (i,)
    return t


def strTuple(param):
    t = '('
    for i in param:
        if i:
            t = t + i + ','
    t = t[:len(t) - 1]
    t += ')'
    return t


def tuple_to_set(tuple):
    set = []
    for i in tuple:
        set.append(i[0])
    return set


def getProducts(products):
    productsTable = []
    for product in products:
        try:
            productAttribute = {}
            attributeType = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_varchar where attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='common_type') and entity_id=" + str(
                    product[0]))
            attributeSetAndSku = db.sqlQuery("""SELECT attribute_set_name, sku FROM firstcatalog_product_entity join firsteav_attribute_set
                on firstcatalog_product_entity.attribute_set_id=firsteav_attribute_set.attribute_set_id
                where entity_id=""" + str(product[0]))
            productName = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_varchar where entity_id=" + str(product[0]) +
                " and attribute_id = (select attribute_id from firsteav_attribute where attribute_code='name' and  entity_type_id=4);")
            productPrice = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_decimal where entity_id=" + str(product[0]) +
                " and attribute_id = (select attribute_id from firsteav_attribute where attribute_code='price' and  entity_type_id=4);")
            productSpecialPrice = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_decimal where entity_id=" + str(product[0]) +
                " and attribute_id = (select attribute_id from firsteav_attribute where attribute_code='special_price' and  entity_type_id=4);")
            productSpecialPriceFrom = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_datetime where entity_id=" + str(product[0]) +
                " and attribute_id = (select attribute_id from firsteav_attribute where attribute_code='special_from_date' and  entity_type_id=4);")
            productSpecialPriceTo = db.sqlQuery(
                "SELECT value FROM firstcatalog_product_entity_datetime where entity_id=" + str(product[0]) +
                " and attribute_id = (select attribute_id from firsteav_attribute where attribute_code='special_to_date' and  entity_type_id=4);")
            productQty = db.sqlQuery(
                "SELECT qty FROM firstcataloginventory_stock_item where product_id=" + str(product[0]))
            if attributeType:
                t = verifyTuple(attributeType[0])
                if t:
                    type = db.sqlQuery(
                        "SELECT distinct value FROM firsteav_attribute_option_value where option_id in " + strTuple(
                            t))
                    productAttribute["type"] = type[0][0].split(',')
                else:
                    productAttribute["type"] = ""
            else:
                productAttribute["type"] = ""
            productAttribute["attribute_set"] = attributeSetAndSku[0][0]
            productAttribute["sku"] = attributeSetAndSku[0][1]
            productAttribute["name"] = productName[0][0]
            productAttribute["price"] = round(float(productPrice[0][0]), 2)
            productAttribute["price_usd"] = round(float(productPrice[0][0]) / currency_rate, 2)
            if productSpecialPrice[0][0]:
                productAttribute["special_price"] = round(float(productSpecialPrice[0][0]), 2)
                productAttribute["special_price_usd"] = round(float(productSpecialPrice[0][0]) / currency_rate, 2)
            if productSpecialPriceFrom[0][0]:
                productAttribute["special_from_date"] = str(productSpecialPriceFrom[0][0])
            else:
                productAttribute["special_from_date"] = ""
            if productSpecialPriceTo[0][0]:
                productAttribute["special_to_date"] = str(productSpecialPriceTo[0][0])
            else:
                productAttribute["special_to_date"] = ""
            productAttribute["qty"] = int(productQty[0][0])

            productsTable.append(productAttribute)
        except Exception:
            pass
    sets = db.sqlQuery("SELECT distinct attribute_set_name FROM firsteav_attribute_set")
    types = db.sqlQuery(
        "SELECT distinct value FROM firsteav_attribute_option_value where option_id in (SELECT option_id FROM firsteav_attribute_option where attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='common_type'))")

    return productsTable, tuple_to_set(sets), tuple_to_set(types)


def getProductTablebyUsername(username):
    role, userId = getUsernameRole(username)
    if role == 5:
        products = db.sqlQuery("select product_id from firstadmin_product where admin_id=" + userId)
    elif role == 1:
        products = db.sqlQuery("select entity_id from firstcatalog_product_entity")
    return getProducts(products)


def getUsernameRole(username):
    userId = db.sqlQuery("select user_id from firstadmin_user where username=\'" + username + "\'")
    role = db.sqlQuery("select parent_id from firstadmin_role where user_id=" + str(userId[0][0]))
    return role[0][0], str(userId[0][0])


def getShopmakers():
    shopmakers = db.sqlQuery("select role_name from firstadmin_role where parent_id=5")
    return tuple_to_set(shopmakers)


def rand_string(length=5, chr_set=string.ascii_uppercase + string.digits):
    output = ''
    for n in range(length):
        output += random.choice(chr_set)
    return output


def saveFile(filePath, filename, content, format="csv"):
    while os.path.exists(filename):
        filename = filename.replace(".csv", "")
        filename = filename + rand_string() + "." + format()
    if not os.path.exists(filePath):
        os.makedirs(filePath)
    files = codecs.open(filePath + filename, 'w', encoding="utf-8")
    files.write(content)
    files.close()
    return filename


def isFloat(string):
    return string.replace('.', '', 1).isdigit()


def whatType(cell):
    type = ""
    try:
        float(cell)
        type = "Number"
    except ValueError:
        type = "String"
    return type


def csvToXml(csvContent):
    xmlContent = """<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office"
          xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">
    <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"></OfficeDocumentSettings>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"></ExcelWorkbook>
    <Worksheet ss:Name="Sheet 1">
        <Table><Row><Cell><Data ss:Type="String"></Data></Cell><Cell><Data ss:Type="String">main_type</Data></Cell><Cell>
    <Data ss:Type="String">attribute_set</Data></Cell><Cell><Data ss:Type="String">sku</Data></Cell><Cell><Data ss:Type="String">name</Data>
    </Cell><Cell><Data ss:Type="String">price</Data></Cell><Cell><Data ss:Type="String">special_price</Data></Cell><Cell>
    <Data ss:Type="String">special_from_date</Data></Cell><Cell><Data ss:Type="String">special_to_date</Data></Cell></Row>"""

    csvRow = csvContent.split("\r\n")
    del csvRow[0]
    del csvRow[len(csvRow) - 1]
    for row in csvRow:
        xmlContent += """<Row>"""
        row = row[:len(row) - 1].split("\",\"")
        for cell in row:
            xmlContent += """<Cell><Data ss:Type=\"""" + whatType(cell) + """\">""" + cell + """</Data></Cell>"""
        xmlContent += """</Row>"""
    xmlContent += """</Table></Worksheet></Workbook>"""

    return xmlContent


def  getCSVHistory(shopmaker):
    listCSVUser = []
    pathTemp = STATICFILES_DIRS[0] + "/csv/temp/" + shopmaker
    pathApprove = STATICFILES_DIRS[0] + "/csv/" + shopmaker
    if os.path.exists(pathApprove):
        for csv in os.listdir(pathApprove):
            if csv.find(".csv") != -1:
                listCSVUser.append({"date": csv.replace(".csv", "").replace("_", " "), "status": "принят"})
    if os.path.exists(pathTemp):
        for csv in os.listdir(pathTemp):
            if csv.find(".csv") != -1:
                listCSVUser.append({"date": csv.replace(".csv", "").replace("_", " "), "status": "не проверен"})
    return listCSVUser


def getFilePath(filename, type, path=STATICFILES_DIRS[0]):
    dirs = os.listdir(path)
    fullFilename = fnmatch.filter(dirs, filename + type)
    if fullFilename:
        return fullFilename[0]
    for dir in dirs:
        if '.' not in dir:
            fullFilename = getFilePath(filename, type=type, path=path + "/" + dir)
            if fullFilename:
                return dir + '/' + fullFilename


def editFile(filename, shopmaker):
    content = {'shopmaker': shopmaker, 'currency': currency_rate}
    content['filepath'] = STATIC_URL + getFilePath(filename.replace(" ", "_"), type='.csv')
    content['fileXML'] = STATIC_URL + getFilePath(filename.replace(" ", "_"), type='.xml')
    return content


def viewFile(filename, shopmaker):
    content = {'shopmaker': shopmaker, 'currency': currency_rate}
    content['filepath'] = STATIC_URL + getFilePath(filename.replace(" ", "_"), type='.csv')
    content['fileXML'] = STATIC_URL + getFilePath(filename.replace(" ", "_"), type='.xml')
    return content


def deleteFile(filename, shopmaker):
    filepathXML = STATICFILES_DIRS[0] + getFilePath(filename, '.xml')
    filepathCSV = STATICFILES_DIRS[0] + getFilePath(filename, '.csv')
    os.remove(filepathCSV)
    os.remove(filepathXML)


def approveFile(filename, shopmaker):
    filepathCSV = STATICFILES_DIRS[0] + getFilePath(filename, '.csv')
    os.rename(filepathCSV, filepathCSV.replace('temp/', ''))


def cancelFile(filename, shopmaker):
    filepathCSV = STATICFILES_DIRS[0] + getFilePath(filename, '.csv')
    os.rename(filepathCSV, filepathCSV.replace('csv/', 'csv/temp/'))


def historyAction(action, filename, shopmaker):
    if action == "изменить":
        return editFile(filename, shopmaker)
    elif action == "просмотреть":
        return viewFile(filename, shopmaker)
    elif action == "удалить":
        return deleteFile(filename, shopmaker)
    elif action == "подтвердить":
        return approveFile(filename, shopmaker)
    elif action == "отменить":
        return cancelFile(filename, shopmaker)
